from pymysql import  *
import time
import datetime
from pymysql import connect

conn = connect(host='1358294575.mysql.pythonanywhere-services.com',
                user='1358294575',
                password='API123456',
                port=3306,
                database="1358294575$apidemo"
                )  #连接数据库


#先根据号码查是否存在，如果有则已经注册，否则组装sql，注册。
def regist_user(tel,name,password):
    cs1 = conn.cursor()
    sql="select * from user where tel ='{}';".format(tel)      #根据号码组装sql
    try:
        c = cs1.execute(sql)              #执行sql 查询是否已经被注册
        print(sql)
        if c == 0:             #如果没用查到证明可以继续注册 插入用户信息
            sql= "insert into user values('{}','{}','{}');".format(tel,name,password)
            print(sql)
            try:
                cs1.execute(sql)   #插入新增用户信息
                conn.commit()
                return True
            except Exception as e:
                print(e)
                return False

        else:
            print ("已经注册")
            return False
    except Exception as e:
        print("查询sql有问题",sql)
        print (e)
        return False


    cs1.close()
    conn.close()


def seach_pass(tel,name):            #用于找回密码  根据 输入的号码和姓名去查查到返回密码
    cs1 = conn.cursor()
    sql = "select * from user where tel ='{}' and name ='{}';".format(tel,name)
    try:
        count = cs1.execute(sql)      #执行sql 查询用户密码
        if count == 1:
            print(sql)
            result = cs1.fetchall()
            #print (result[0][2])
            return  result[0][2]   #返回密码
        else:
            print("没有该账户")
            return  False

    except Exception as e:
        print(e)
        return  False

    cs1.close()
    conn.close()


def check_login(tel,password):            #登录核验账户密码，对了返回TRUE错了返回flase
    cs1 = conn.cursor()
    sql = "select * from user where tel ='{}' and password ='{}';".format(tel, password)
    print (sql)
    try:
        count = cs1.execute(sql)   #执行查询用户sql
        if count == 1:
            result = cs1.fetchall()
            #print(result[0][1])
            return result[0][1]   #有返回用户名
        else:
            print("没有该账户")
            return False

    except Exception as e:
        print("aa")
        print(e)
        return False


def insert_record(id,type,result):            #插入操作记录
    cs1 = conn.cursor()
    dt_ms = datetime.datetime.now()          #获取当前时间
    #print(str(dt_ms))
    sql = """insert into record values("{}","{}","{}","{}");""".format(id,type,result,str(dt_ms)) #组装sql
    #print(sql)
    try:
        cs1.execute(sql)  #插入sql
        conn.commit()
    except Exception as e:
        print(e)
        return False

def search_record(id):
    cs1 = conn.cursor()
    sql = """select * from  record where tel="{}";""".format(id)  #根据用户id 去查找操作记录
    print(sql)
    try:
        cs1.execute(sql)
        result = cs1.fetchall()
        return result  #返回数据库的结果
    except Exception as e:
        print(e)
        return False


